package dataHandlers.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class ForumSchemeCreator {

	public ForumSchemeCreator(){}
	
	public static void createDataBase(Connection conn) throws SQLException{
		Statement s = conn.createStatement();
		s.executeUpdate("CREATE SCHEMA sadna");
		s.close();	
	}
	
	
	public static void createMembersTbl(Connection conn) throws SQLException{
		Statement s = conn.createStatement();
		s.executeUpdate("CREATE  TABLE `sadna`.`members` ("
						  +"userName VARCHAR(45) NOT NULL ,"
						  +"`id` INT NOT NULL ,"
						  +"`email` VARCHAR(45) NOT NULL ,"
						  +"`firstName` VARCHAR(45) NOT NULL ,"
						  +"`lastName` VARCHAR(45) NOT NULL ,"
						  +"PRIMARY KEY (`userName`) ) ");
		s.close();
	}
	public static void createForumsTbl(Connection conn) throws SQLException{
		Statement s = conn.createStatement();
		s.executeUpdate("CREATE  TABLE `sadna`.`forums` ("
				+"`id` INT NOT NULL AUTO_INCREMENT ,"
				+"`name` VARCHAR(45) NOT NULL ,"
				+"`creator` VARCHAR(45) NOT NULL ,"
				+"PRIMARY KEY (`id`) ,"
				+"INDEX `forumCreator` (`creator` ASC) ,"
				+"CONSTRAINT `forumCreator`"
				+"FOREIGN KEY (`creator` )"
				+"REFERENCES `sadna`.`members` (`userName` )"
				+"ON DELETE NO ACTION "
				+"ON UPDATE NO ACTION )");
		s.close();
	}
	
	public static void createPostsTbl(Connection conn) throws SQLException{
		Statement s = conn.createStatement();
		s.executeUpdate("CREATE  TABLE `sadna`.`posts` ("
				+"`id` INT NOT NULL AUTO_INCREMENT ,"
				+"`title` VARCHAR(45) NOT NULL ,"
				+"`content` VARCHAR(45) NOT NULL ,"
				+"`creationTime` DATETIME NOT NULL ,"
				+"`editingTime` DATETIME NOT NULL ,"
				+"`creator` VARCHAR(45) NOT NULL ,"
				+"`topicId` INT NOT NULL ,"
				+"PRIMARY KEY (`id`) ,"
				+"INDEX `postTopic` (`topicId` ASC) ,"
				+"INDEX `postCreator` (`creator` ASC) ,"
				+"CONSTRAINT `postTopic`"
				+"FOREIGN KEY (`topicId` )"
				+"REFERENCES `sadna`.`topics` (`id` )"
				+"ON DELETE NO ACTION "
				+"ON UPDATE NO ACTION,"
				+"CONSTRAINT `postCreator`"
				+"FOREIGN KEY (`creator` )"
				+"REFERENCES `sadna`.`members` (`userName` )"
				+"ON DELETE NO ACTION "
				+"ON UPDATE NO ACTION)");
		s.close();
	}	
	
	public static void createTopicsTbl(Connection conn) throws SQLException{
		Statement s = conn.createStatement();
		s.executeUpdate("CREATE  TABLE `sadna`.`topics` ("
				+"`id` INT NOT NULL AUTO_INCREMENT ,"
				+"`title` VARCHAR(45) NOT NULL ,"
				+"`creationTime` DATETIME NOT NULL ,"
				+"`creator` VARCHAR(45) NOT NULL ,"
				+"`forumId` INT NOT NULL ,"
				+"PRIMARY KEY (`id`) ,"
				+"INDEX `topicCreator` (`creator` ASC) ,"
				+"INDEX `topicForum` (`forumId` ASC) ,"
				+"CONSTRAINT `topicCreator`"
				+"FOREIGN KEY (`creator` )"
				+"REFERENCES `sadna`.`members` (`userName` )"
				+"ON DELETE NO ACTION "
				+"ON UPDATE NO ACTION,"
				+"CONSTRAINT `topicForum`"
				+"FOREIGN KEY (`forumId` )"
				+"REFERENCES `sadna`.`forums` (`id` )"
				+"ON DELETE NO ACTION "
				+"ON UPDATE NO ACTION)");
		s.close();
	}
	
	
	// ********************************* MARINA ********************************** 
	public static void createFriendsTbl(Connection conn) throws SQLException{
		Statement s  = conn.createStatement();
		s.executeUpdate("CREATE  TABLE `sadna`.`friends` (" 
				+"`username` VARCHAR(45) NOT NULL ,"
				+"`friends_username` VARCHAR(45) NOT NULL ,"
				+"PRIMARY KEY (`username`, `friends_username`) ,"
				+"INDEX `username` (`username` ASC) ,"  
				+"INDEX `friends_username` (`friends_username` ASC, `username` ASC) ," 
				+"CONSTRAINT `username` "
				+"FOREIGN KEY (`username` ) "  
				+"REFERENCES `sadna`.`members` (`userName` ) "  		    
				+"ON DELETE NO ACTION "    
				+"ON UPDATE NO ACTION,"
				+"CONSTRAINT `friends_username` "    
		    	+" FOREIGN KEY (`friends_username` ) "	     
		    	+"REFERENCES `sadna`.`members` (`userName`) "
				+" ON DELETE NO ACTION "    
				+"ON UPDATE NO ACTION)");
		s.close();		 
	}
	
	public static void test(Connection conn) throws SQLException{
		Statement s = conn.createStatement();
		String INSERT_SQL = "INSERT INTO `sadna`.`forums` (`name`, `creator`) VALUES ('jobsss', 'nir')";
		//s.executeUpdate(INSERT_SQL);
		
		s.executeUpdate(INSERT_SQL, Statement.RETURN_GENERATED_KEYS);
		ResultSet rs = s.getGeneratedKeys();

		
		//s.executeUpdate(arg0, arg1)
		//ResultSet rs = s.getResultSet(); //eror
		System.out.println(rs.getInt(1));//eror
		s.close();
	}
	
    public static void main (String[] args)
    {
        Connection conn = null;
        try
        {
            String userName = "root";
            String password = "sadna";
            String url = "jdbc:mysql://localhost:3306/";
            Class.forName ("com.mysql.jdbc.Driver").newInstance();
            conn = DriverManager.getConnection (url, userName, password);
            System.out.println ("Database connection established");
            createDataBase(conn);
            createMembersTbl(conn);
            createForumsTbl(conn);
            createTopicsTbl(conn);
            createPostsTbl(conn);
            createFriendsTbl(conn);
       //     test(conn);
        }
        catch (Exception e)
        {
            System.err.println ("Cannot connect to database server");
            System.err.println (e.getMessage());
        }
        finally
        {
            if (conn != null)
            {
                try
                {
                    conn.close ();
                    System.out.println ("Database connection terminated");
                }
                catch (Exception e) { /* ignore close errors */ }
            }
        }
    }
}
